USE YongTest;
go
Print 'YongTest Database Selected';

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].InactivePatient') AND type in (N'V'))
Begin
	drop view InactivePatient;
	Print 'Table Orders Dropped';
End
Go

CREATE TABLE Patients   (      PatientID      INT NOT NULL PRIMARY KEY,      PatientName    VARCHAR(255) NOT NULL,      PatientAddress VARCHAR(1024)   ) 
CREATE TABLE Prescriptions   (      PrescriptionID   INT NOT NULL PRIMARY KEY,      PrescriptionText VARCHAR(255) NOT NULL,      PatientID        INT FOREIGN KEY REFERENCES Patients(PatientID)   ) 
CREATE TABLE Visits   (      VisitID     INT NOT NULL PRIMARY KEY,      VisitReason VARCHAR(255) NOT NULL,      PatientID   INT FOREIGN KEY REFERENCES Patients(PatientID)   )
go

Create view InactivePatient
as 
Select 
	PatientName,
	PatientAddress
	from 
	Patients
	where Patients.PatientID NOT IN (SELECT DISTINCT Patients.PatientID
	FROM   Patients
	INNER JOIN Prescriptions
	ON Patients.PatientID = Prescriptions.PatientID
	UNION
	SELECT 
		DISTINCT Patients.PatientID
	FROM   
		Patients
	INNER JOIN 
		Visits
		ON
		Patients.PatientID = Visits.PatientID)
